#! /usr/bin/perl -w

use strict;
use DBI;

my $filename = $ARGV[0];

# ---------------------------------
# This part reads the db-settnings.
# ---------------------------------

my $db_config_path = "../Perl/db_settings.txt";

#### Open input file
open DB_FILE, "<$db_config_path"
	or die "Something's wrong with the db config file!\n";

my $db_line = <DB_FILE>;		# holds the current line from the input file
my @db_conf_arr = split(/\s+/, $db_line);
my $username = shift @db_conf_arr;
my $password = shift @db_conf_arr;
my $database = shift @db_conf_arr;

close DB_FILE
        or die "Problem closing db-config file.\n";

# ----------------------------------------
# End reading db-settings.
# ----------------------------------------

### Create a connection to the database
my $dbh = DBI->connect(
    		"DBI:mysql:$database:localhost:3306",
    		$username,
    		$password
);


### Remove previous clinical data table.

my $drop_handle = $dbh->prepare_cached("DROP TABLE IF EXISTS clinical_data")
    or die "Couldn't prepare statment for DROP clinical_data.";

$drop_handle->execute()
    or die "Couldn't execute DROP TABLE on clinical data.";

#### OPEN OUR INPUT FILE
open INPUT_FILE, "<$filename"
	or die "Something's wrong with the input file!\n ";

my $line = <INPUT_FILE>; # holds the current line from the input file
chomp $line;
### Remove all " in the first line of file.
$line =~ s/\"//g;
### store first line of file in the array variables.
my @variables = split(/\s+/, $line);
### move to nex line
$line = <INPUT_FILE>;

### Remove all " and replace _ with 0000 in the rest of the file.
### This is written to a new file that is being imported into mysql in the end of this script.

## Open file that is going to be appended to
open (MYFILE, '>>clinical_data_mod.txt');

while (defined $line){
chomp $line;
$line =~ s/_/0000/g;
$line =~ s/\"//g;
print MYFILE "$line\n";
$line = <INPUT_FILE>;
}
close (MYFILE);

### The new modified clinical_data file is finished.
### This file will be removed in the end of this script.

### Remove first white space.
shift @variables;

### Create table.
my $create_query = "CREATE TABLE clinical_data (`ind_nbr` INT(8) NOT NULL PRIMARY KEY, ";
my $current_var = shift @variables;
while (defined $current_var)
{
    $create_query = $create_query . "`" . $current_var . "`" . " TEXT,";
    $current_var = shift @variables;
}

$create_query = $create_query . ")";
$create_query =~ s/(.*),\)\Z/$1\)/;

my $create_handle = $dbh->prepare_cached($create_query);
$create_handle->execute();

my $insert_statment = "LOAD DATA LOCAL INFILE '" . "clinical_data_mod.txt" . "' INTO TABLE clinical_data FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n'";
my $insert_handle = $dbh->prepare_cached($insert_statment)
			  or die "Couldn't prepare LOAD DATA INFILE statment.\n";

$insert_handle->execute()
	or die "Couldn't read " . $filename . " to the database.\n";

$dbh->disconnect();

###Important remove of clinical_data_mod.txt
#system("rm clinical_data_mod.txt");
unlink("clinical_data_mod.txt");